// src/scripts/updatePaymentOrderIdStructure.js
const { sequelize } = require('../config/database');

async function updatePaymentOrderIdStructure() {
  try {
    console.log('🔄 开始更新 payment_order_id 字段结构...');
    
    // 检查当前字段类型
    const [results] = await sequelize.query(`
      SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH 
      FROM INFORMATION_SCHEMA.COLUMNS 
      WHERE TABLE_SCHEMA = DATABASE() 
      AND TABLE_NAME = 'token_transactions' 
      AND COLUMN_NAME = 'payment_order_id'
    `);
    
    if (results.length === 0) {
      console.log('❌ 未找到 payment_order_id 字段');
      return;
    }
    
    const currentField = results[0];
    console.log(`📋 当前字段信息: ${currentField.COLUMN_NAME} - ${currentField.DATA_TYPE}(${currentField.CHARACTER_MAXIMUM_LENGTH})`);
    
    // 如果已经是 UUID 类型，则不需要更新
    if (currentField.DATA_TYPE === 'char' && currentField.CHARACTER_MAXIMUM_LENGTH === 36) {
      console.log('✅ payment_order_id 字段已经是 UUID 类型，无需更新');
      return;
    }
    
    // 检查是否有数据
    const [dataCount] = await sequelize.query(`
      SELECT COUNT(*) as count 
      FROM token_transactions 
      WHERE payment_order_id IS NOT NULL
    `);
    
    console.log(`📊 当前有 ${dataCount[0].count} 条记录包含 payment_order_id`);
    
    if (dataCount[0].count > 0) {
      console.log('⚠️  检测到现有数据，将进行数据迁移...');
      
      // 验证现有数据是否符合 UUID 格式
      const [invalidData] = await sequelize.query(`
        SELECT COUNT(*) as count 
        FROM token_transactions 
        WHERE payment_order_id IS NOT NULL 
        AND payment_order_id NOT REGEXP '^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$'
      `);
      
      if (invalidData[0].count > 0) {
        console.log(`❌ 发现 ${invalidData[0].count} 条记录的 payment_order_id 不符合 UUID 格式`);
        console.log('请先清理或修正这些数据，然后再运行此脚本');
        return;
      }
    }
    
    // 开始更新字段结构
    console.log('🔄 正在更新字段结构...');
    
    // 使用 ALTER TABLE 修改字段类型
    await sequelize.query(`
      ALTER TABLE token_transactions 
      MODIFY COLUMN payment_order_id CHAR(36) NULL COMMENT '关联的支付订单ID'
    `);
    
    console.log('✅ payment_order_id 字段结构更新成功');
    
    // 验证更新结果
    const [verifyResults] = await sequelize.query(`
      SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH 
      FROM INFORMATION_SCHEMA.COLUMNS 
      WHERE TABLE_SCHEMA = DATABASE() 
      AND TABLE_NAME = 'token_transactions' 
      AND COLUMN_NAME = 'payment_order_id'
    `);
    
    const updatedField = verifyResults[0];
    console.log(`📋 更新后字段信息: ${updatedField.COLUMN_NAME} - ${updatedField.DATA_TYPE}(${updatedField.CHARACTER_MAXIMUM_LENGTH})`);
    
    // 添加外键约束（可选）
    console.log('🔄 正在添加外键约束...');
    try {
      await sequelize.query(`
        ALTER TABLE token_transactions 
        ADD CONSTRAINT fk_token_transactions_payment_order 
        FOREIGN KEY (payment_order_id) REFERENCES payment_orders(id) 
        ON DELETE SET NULL ON UPDATE CASCADE
      `);
      console.log('✅ 外键约束添加成功');
    } catch (error) {
      if (error.message.includes('Duplicate key name')) {
        console.log('ℹ️  外键约束已存在，跳过添加');
      } else {
        console.log('⚠️  外键约束添加失败:', error.message);
      }
    }
    
    console.log('🎉 payment_order_id 字段结构更新完成');
    process.exit(0);
    
  } catch (err) {
    console.error('❌ 更新 payment_order_id 字段结构失败:', err);
    process.exit(1);
  }
}

updatePaymentOrderIdStructure(); 